package com.ruoyi.system.mapper;

import java.util.Date;
import java.util.List;

import com.ruoyi.system.domain.EventAndTopic;
import com.ruoyi.system.domain.InformationSchemaColumn;
import com.ruoyi.system.domain.PoPublicOpinion;
import com.ruoyi.system.domain.vo.*;
import org.apache.ibatis.annotations.*;

/**
 * 舆情Mapper接口
 *
 * @author ruoyi
 * @date 2021-06-07
 */
public interface PoPublicOpinionMapper {
    @Select("<script>" +
            "SELECT opinion_id FROM  opinion_forecast where 1 = 1 "+
            "<if test= \"level!=null and level != ''  \">" +
            "and level = #{level} " +
            "</if>" +
            "<if test= \"tenantCode!=null and tenantCode != ''  \">" +
            "and tenant_code = #{tenantCode} " +
            "</if>" +
            "</script>"
    )
    List<String> queryWarningIds(@Param("level") String level,@Param("tenantCode") String tenantCode);




    /**
     * 查询舆情列表
     *
     * @param poPublicOpinion 舆情
     * @return 舆情集合
     */
    public List<PoPublicOpinion> selectPoPublicOpinionList(PoPublicOpinion poPublicOpinion);


    /**
     * 新增舆情（数据库）
     *
     * @param poPublicOpinion 舆情
     * @return 结果
     */
    public int insertPoPublicOpinion(PoPublicOpinion poPublicOpinion);

    /**
     * 修改舆情
     *
     * @param poPublicOpinion 舆情
     * @return 结果
     */
    public int updatePoPublicOpinion(PoPublicOpinion poPublicOpinion);

    /**
     * 删除舆情
     *
     * @param id 舆情ID
     * @return 结果
     */
    public int deletePoPublicOpinionById(Long id);

    /**
     * 批量删除舆情
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    public int deletePoPublicOpinionByIds(Long[] ids);

    /**
     * 查询舆情列表
     *
     * @param vo 舆情参数
     * @return 舆情集合
     */
    public List<PoPublicOpinion> selectPoPublicOpinionListByEvent(EventQueryParameterVo vo);


    List<CountResultVo> queryMediaTypeByEventId(EventQueryParameterVo vo);


    @Select("select DISTINCT(media_type) as media_type from po_public_opinion")
    List<String> queryAllMediaType();







    @Select(
            "<script>"
                    + "SELECT\n" +
                    "\tm.media_name,\n" +
                    "\tcount(p.id) AS op_num\n" +
                    "FROM\n" +
                    "\t(\n" +
                    "\t\tSELECT\n" +
                    "\t\t\tdict_label AS media_name\n" +
                    "\t\tFROM\n" +
                    "\t\t\tsys_dict_data\n" +
                    "\t\tWHERE\n" +
                    "\t\t\tdict_type = 'overseas_media'\n" +
                    "\t) m\n" +
                    "LEFT JOIN (\n" +
                    "\tSELECT\n" +
                    "\t\t*\n" +
                    "\tFROM\n" +
                    "\t\tpo_public_opinion\n" +
                    "\tWHERE\n" +
                    "\t\t1 = 1\n" +
                    ") p ON m.media_name = p.media_name\n" +
                    "GROUP BY\n" +
                    "\tm.media_name"
                    + "</script>")
    @Results({
            @Result(column = "media_name", property = "mediaName"),
            @Result(column = "op_num", property = "opNum"),
    })
    List<OpMediaNameNumVM> countMediaOverseasOpinion(@Param("opinionIds") List<String> opinionIds);

    @Select(
            "<script>"
                    + "SELECT\n" +
                    "\tm.language AS name,\n" +
                    "\tcount(p.id) AS value\n" +
                    "FROM\n" +
                    "\t(\n" +
                    "\t\tSELECT\n" +
                    "\t\t\tdict_label AS language\n" +
                    "\t\tFROM\n" +
                    "\t\t\tsys_dict_data\n" +
                    "\t\tWHERE\n" +
                    "\t\t\tdict_type = 'opinion_language'\n" +
                    "\t) m\n" +
                    "LEFT JOIN (\n" +
                    "\tSELECT\n" +
                    "\t\t*\n" +
                    "\tFROM\n" +
                    "\t\tpo_public_opinion\n" +
                    "\tWHERE\n" +
                    "\t\t1 = 1\n" +
                    ") p ON m.language = p.language\n" +
                    "GROUP BY\n" +
                    "\tm.language"
                    + "</script>")
    List<NameValue> getOpinionByLanguageTypeCounts(@Param("opinionIds") List<String> opinionIds);




    @Insert("<script>insert into opinion_forecast(opinion_id, level, user_id, dept_id, tenant_code) values " +
            "<foreach collection='ids' item = 'id' separator=','>(#{id}, #{level}, #{userId}, #{deptId}, #{tenantCode})</foreach></script>")
    int joinTheForecast(@Param("ids") String[] ids,
                        @Param("level") String level,
                        @Param("userId") Long userId,
                        @Param("deptId") Long deptId,
                        @Param("tenantCode") String tenantCode);


    @Delete("<script>delete from opinion_forecast where 1=1 " +
            "<if test='userId!=null'>" +
            " and user_id=#{userId} "+
            "</if>" +
            "<if test='deptId!=null'>" +
            " and dept_id=#{deptId} "+
            "</if>" +
            "<if test='tenantCode!=null'>" +
            " and tenant_code=#{tenantCode} "+
            "</if>" +
            " and opinion_id in <foreach collection = 'ids' item ='id' index ='index' open='(' close=')' separator=','>#{id}</foreach></script>")
    int removeTheForecast(@Param("ids") String[] ids,
                          @Param("userId") Long userId,
                          @Param("deptId") Long deptId,
                          @Param("tenantCode") String tenantCode);










    @Insert("<script>insert into opinion_favorites(opinion_id, join_time, user_id, dept_id, tenant_code) values " +
            "<foreach collection='ids' item = 'id' separator=','>(#{id}, #{joinTime}, #{userId}, #{deptId}, #{tenantCode})</foreach></script>")
    int addToFavorites(@Param("ids") String[] ids, @Param("joinTime") Date joinTime,
                       @Param("userId") Long userId,
                       @Param("deptId") Long deptId,
                       @Param("tenantCode") String tenantCode);

    @Delete("<script>delete from opinion_favorites where 1=1 " +
            "<if test='userId!=null '>" +
            " and user_id =#{userId} " +
            "</if>" +
            " and opinion_id in " +
            "<foreach collection='ids' item ='id' index ='index' open='(' close=')' separator=','>#{id}</foreach></script>")
    int removeFavorites(@Param("ids") String[] ids,
                        @Param("userId") Long userId,
                        @Param("deptId") Long deptId,
                        @Param("tenantCode") String tenantCode);





    @Select("SELECT\n" +
            "\tDATE_FORMAT(publish_date, \"%Y-%m\") AS months\n" +
            "FROM\n" +
            "\tpo_public_opinion\n" +
            "GROUP BY\n" +
            "\tDATE_FORMAT(publish_date, \"%Y-%m\")\n" +
            "ORDER BY\n" +
            "\tDATE_FORMAT(publish_date, \"%Y-%m\") ASC")
    List<String> getMonths();



    @Select("SELECT\n" +
            "\tmedia_type\n" +
            "FROM\n" +
            "\tpo_media_type " +
            "ORDER BY id "
    )
    List<String> getPoMediaTypes();




    @Select("SELECT\n" +
            " zz.months,\n" +
            " m1 media_type,\n" +
            " IFNULL(num, 0) count,\n" +
            " media_type_id\n" +
            "FROM\n" +
            " (\n" +
            "  SELECT\n" +
            "   months,\n" +
            "   m1,\n" +
            "   media_type_id\n" +
            "  FROM\n" +
            "   (\n" +
            "    SELECT\n" +
            "     distinct(SUBSTR(publish_day, 1, 7)) AS months\n" +
            "     \n" +
            "    FROM\n" +
            "     po_publish_day\n" +
            "\n" +
            "    \n" +
            "   ) xx,\n" +
            "   (\n" +
            "    SELECT\n" +
            "     media_type m1,\n" +
            "     id media_type_id " +
            "    FROM\n" +
            "     po_media_type\n" +
            "    ORDER BY id " +
            "    \n" +
            "   ) yy\n" +
            " ) zz\n" +
            "LEFT JOIN (\n" +
            " SELECT\n" +
            "  DATE_FORMAT(publish_date, \"%Y-%m\") AS dates,\n" +
            "  media_type m2,\n" +
            "  COUNT(*) num\n" +
            " FROM\n" +
            "  po_public_opinion \n" +
            "\tWHERE\n" +
            "\tsensitive_information = #{sensitive_information} \n" +
            " GROUP BY\n" +
            "  DATE_FORMAT(publish_date, \"%Y-%m\"),\n" +
            "  m2\n" +
            ") kk ON zz.months = kk.dates\n" +
            "AND zz.m1 = kk.m2\n" +
            "ORDER BY\n" +
            " months,\n" +
            " media_type_id")
    List<MonthMediaTypeCount> getDataByMonthMediaTypeCount(@Param("sensitive_information") String sensitiveInformation);

    @Select("SELECT\n" +
            "\tzz.months,\n" +
            "\tm1 media_type,\n" +
            "\tIFNULL(num, 0) count,\n" +
            " media_type_id\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tmonths,\n" +
            "\t\t\tm1,\n" +
            "   media_type_id\n" +
            "\t\tFROM\n" +
            "\t\t\t(\n" +
            "\t\t\t\tSELECT DISTINCT\n" +
            "\t\t\t\t\t(SUBSTR(publish_day, 1, 7)) AS months\n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\t\tpo_publish_day\n" +
            "\t\t\t) xx,\n" +
            "\t\t\t(\n" +
            "\t\t\t\tSELECT\n" +
            "\t\t\t\t\tmedia_type m1,\n" +
            "     id media_type_id " +
            "\t\t\t\tFROM\n" +
            "\t\t\t\t\tpo_media_type\n" +
            "        ORDER BY id " +
            "\t\t\t) yy\n" +
            "\t) zz\n" +
            "LEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tDATE_FORMAT(publish_date, \"%Y-%m\") AS dates,\n" +
            "\t\tmedia_type m2,\n" +
            "\t\tCOUNT(*) num\n" +
            "\tFROM\n" +
            "\t\tpo_public_opinion p\n" +
            "\tLEFT JOIN opinion_forecast f ON p.id = f.opinion_id\n" +
            "\tWHERE\n" +
            "\t\tf.`level` = #{level}\n" +
            "\tGROUP BY\n" +
            "\t\tDATE_FORMAT(publish_date, \"%Y-%m\"),\n" +
            "\t\tm2\n" +
            ") kk ON zz.months = kk.dates\n" +
            "AND zz.m1 = kk.m2\n" +
            "ORDER BY\n" +
            "\tmonths,\n" +
            "\tmedia_type_id")
    List<MonthMediaTypeCount> getDataByMonthMediaTypeCountForecast(@Param("level") String level);


    @Select("<script>" +
            "SELECT opinion_id from opinion_favorites\n"+
            "\tWHERE 1=1 \n" +
            "\t<if test='tenantCode!=null '>\n" +
            "\tand tenant_code = #{tenantCode} \n" +
            "\t</if>\n" +
            "</script>"
    )
    List<String> getFavorites(@Param("tenantCode") String tenantCode);



    @Select("<script>" +
            "SELECT event_id as id,event_title as title,related_words,created_time,'event' as type\n" +
            "FROM public_opinion_events\n" +
            "\tWHERE 1=1 \n" +
            "\t<if test='tenantCode!=null '>\n" +
            "\tand tenant_code = #{tenantCode} \n" +
            "\t</if>\n" +
            "UNION\n" +
            "SELECT id,title,related_words,created_time,'topic' as type\n" +
            "FROM public_opinion_topic\n"+
            "\tWHERE 1=1 \n" +
            "\t<if test='tenantCode!=null '>\n" +
            "\tand tenant_code = #{tenantCode} \n" +
            "\t</if>\n" +
            "</script>"
    )
    @Results({
            @Result(column = "related_words", property = "relatedWords"),
            @Result(column = "created_time", property = "createdTime"),
    })
    List<EventAndTopic> queryEventAndTopic(@Param("tenantCode") String tenantCode);
}
